# -*- coding:utf-8 -*-
"""
Author：Administrator
Date:2021年10月14日
"""
import pandas as pd
import numpy as np

## 1.提取楼层剪力
path = './数据/附加阻尼比计算原始数据.xlsx'

data_sf = pd.read_excel(path, sheet_name='楼层剪力', skiprows=2, usecols=[0, 2, 3, 4], names=['层', '时程', 'X向剪力(kN)',
                                                                                          'Y向剪力(kN)'])

# 去除不包含屋顶行的数据
data_sf = data_sf[~data_sf['层'].str.contains('屋顶')]
# 筛选只包含'全部'的数据行
con = data_sf['时程'].str.contains('全部')
data_sf = data_sf[con].reset_index(drop=True)

data_sf.loc[data_sf['时程'].str.contains('X'), 'Vi(kN)'] = data_sf['X向剪力(kN)']
data_sf.loc[data_sf['时程'].str.contains('Y'), 'Vi(kN)'] = data_sf['Y向剪力(kN)']

data_sf['Vi(kN)'] = abs(data_sf['Vi(kN)'])
data_sf = data_sf.drop(labels=['X向剪力(kN)', 'Y向剪力(kN)'], axis=1)
# print(data_sf)
#
# data_sf.to_excel('./数据/01结果-剪力.xlsx', index=False)


##2.提取X向层间位移
data_xd = pd.read_excel(path, sheet_name='X向位移角', skiprows=1, usecols=[0, 1, 6], names=['时程', '层', 'Δui(mm)'])
# 筛选只包含'X(全部)'的数据行
con = data_xd['时程'].str.contains('X\(全部\)', regex=True)
data_xd = data_xd[con].reset_index(drop=True)
data_xd['Δui(mm)'] = abs(data_xd['Δui(mm)'])

##3.提取Y向层间位移
data_yd = pd.read_excel(path, sheet_name='Y向位移角', skiprows=1, usecols=[0, 1, 6], names=['时程', '层', 'Δui(mm)'])
# 筛选只包含'X(全部)'的数据行
con = data_yd['时程'].str.contains('Y\(全部\)', regex=True)
data_yd = data_yd[con].reset_index(drop=True)
data_yd['Δui(mm)'] = abs(data_yd['Δui(mm)'])
# print(data_yd)
# data_yd.to_excel('./数据/03结果-Y向位移.xlsx', index=False)

##4.合并XY向层间位移

data_xd = pd.concat([data_xd, data_yd])
data_xd.reset_index(inplace=True, drop=True)

# print(data_xd)
#
# data_dis.to_excel('./数据/04结果-XY向位移.xlsx')

##5. 合并剪力与位移数据
data_yd = pd.merge(data_sf, data_xd, on=['层', '时程'])
data_yd['Wsi(kN·mm)'] = (0.5 * data_yd['Vi(kN)'] * data_yd['Δui(mm)']).map(lambda x: "%.2f" % x)
data_yd['Δui(mm)'] = data_yd['Δui(mm)'].map(lambda x: "%.2f" % x)
data_yd['Vi(kN)'] = data_yd['Vi(kN)'].astype('int')
data_yd.set_index('层',inplace=True)


#
# data_yd.to_excel('./数据/05结果-剪力位移.xlsx', index=False)

## 拆分表格
table_name = list(data_yd['时程'].drop_duplicates())
# print(table_name)
new_data = pd.ExcelWriter('./数据/06结果-势能.xlsx')

for i in table_name:
	data1 = data_yd[data_yd['时程'] == i]
	data1 = data1.drop('时程', axis=1)
	data1.reset_index('层')
	data1 = data1.copy()
	data1['Wsi(kN·mm)'] = data1['Wsi(kN·mm)'].astype('float64')
	data1.loc['势能合计'] = data1[['Wsi(kN·mm)']].agg('sum')
	data1.to_excel(new_data, sheet_name=i.replace('全部', '势能'))

new_data.save()
new_data.close()
